package br.ufrn.opencopi.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import br.cnpq.webflowah.beans.ServicoSemantico;
import br.cnpq.webflowah.beans.ServicoSemanticoContexto;
import br.ufrn.opencopi.exceptions.ServiceNotExistException;

/**
 * Armazena apenas o nome do serviço, sua uri, se eh context service e a
 * location (em caso de ser context service).
 * 
 * @author fred
 * 
 */
public class ServiceDB {
	private static Connection connection = null;
	private static Map<String, ServicoSemantico> servicoSemanticoLocator = new HashMap<String, ServicoSemantico>();
	private static final Logger log = Logger.getLogger(ServiceDB.class);

	public static void clearLocator() {
		if (servicoSemanticoLocator != null)
			servicoSemanticoLocator.clear();
	}

	private static Connection getConnection() {
		try {
			if (connection != null && !connection.isClosed())
				return connection;
			else
				Class.forName("org.h2.Driver");
			connection = DriverManager
					.getConnection("jdbc:h2:~/test", "sa", "");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return connection;
	}

	public static void insertService(ServicoSemantico srvc) throws SQLException {
		ServicoSemantico service = ServiceDB.getService(srvc.getURI());
		if (service == null) {// servico ainda nao existente no bd
			Connection connection = null;
			PreparedStatement pstm = null;

			try {
				connection = getConnection();
				String sql = "insert into service (uri, name, iscontextservice, location, isrollbackable, "
						+ "iscompactionable, isdependent) "
						+ "values (?, ?, ?, ?, ?, ?, ?)";
				pstm = connection.prepareStatement(sql);

				pstm.setString(1, srvc.getURI());
				pstm.setString(2, srvc.getNome());
				if (srvc instanceof ServicoSemanticoContexto) {
					pstm.setBoolean(3, true);
					ServicoSemanticoContexto servCont = (ServicoSemanticoContexto) srvc;
					pstm.setString(4, servCont.getLocation());
				} else {
					pstm.setBoolean(3, false);
					pstm.setString(4, "No Location");
				}
				pstm.setBoolean(5, srvc.isRollbackable());
				pstm.setBoolean(6, srvc.isCondAction());
				pstm.setBoolean(7, srvc.isDependent());

				if (srvc.isDependent())
					ServiceDependenciesDB.insertDependenciesByService(
							srvc.getURI(), srvc.getDependencies());

				pstm.execute();
				pstm.close();
			} catch (Exception e) {
				System.err.println("Erro: ServiceDB/insertService()");
				e.printStackTrace();
			} finally {
				pstm.close();
				connection.close();
			}
		} else
			// caso jah exista, soh atualiza.
			ServiceDB.updateService(service);
	}

	public static List<ServicoSemantico> listServices() throws SQLException {
		Connection connection = null;
		PreparedStatement pstm = null;
		ResultSet result = null;
		List<ServicoSemantico> services = null;

		try {
			connection = getConnection();
			String sql = "select * from service order by name, uri";

			pstm = connection.prepareStatement(sql);
			result = pstm.executeQuery();

			services = new ArrayList<ServicoSemantico>();

			while (result.next()) {

				ServicoSemantico service;

				if (!result.getBoolean("iscontextservice")) {
					service = new ServicoSemantico();
					service.setURI(result.getString("uri"));
					service.setNome(result.getString("name"));
				} else {
					service = new ServicoSemanticoContexto();
					((ServicoSemanticoContexto) service).setLocation(result
							.getString("location"));
					service.setURI(result.getString("uri"));
					service.setNome(result.getString("name"));
				}

				// recuperando os metadados do servico. Note que o servico já é
				// retornado completo,
				// com seus dados básicos e seus metadados.
				service.setMetadata(QualityParameterDB
						.listParametersByService(service.getURI()));

				service.setRollbackable(result.getBoolean("isrollbackable"));
				service.setCondAction(result.getBoolean("iscompactionable"));
				service.setDependent(result.getBoolean("isdependent"));

				if (service.isDependent())
					service.setDependencies(ServiceDependenciesDB
							.listServiceDependencies(service.getURI()));

				// add o serviço à colletcion
				services.add(service);
			}

		} catch (Exception e) {
			System.err.println("Erro: ServiceDB/listServices()");
			e.printStackTrace();
		} finally {
			result.close();
			pstm.close();
			connection.close();
		}
		// Adicionar todas as entradas a servicoSemanticoLocator.putAll(...)
		Iterator<ServicoSemantico> i = services.iterator();
		while (i.hasNext()) {
			ServicoSemantico aux = i.next();
			servicoSemanticoLocator.put(aux.getURI(), aux);
		}
		return services;
	}

	public static void updateService(ServicoSemantico service)
			throws SQLException {
		if (servicoSemanticoLocator.containsKey(service.getURI())) {
			servicoSemanticoLocator.remove(service).getURI();
			servicoSemanticoLocator.put(service.getURI(), service);
			log.debug("Replace " + service.getURI());
		}
		Connection connection = null;
		PreparedStatement pstm = null;

		try {
			connection = getConnection();
			String sql = "update service set name = ?, iscontextservice = ?, location = ?, isrollbackable = ?"
					+ ", iscompactionable = ?, isdependent = ? where uri = ? ";
			pstm = connection.prepareStatement(sql);

			pstm.setString(1, service.getNome());
			if (service instanceof ServicoSemanticoContexto) {
				pstm.setBoolean(2, true);
				pstm.setString(3,
						((ServicoSemanticoContexto) service).getLocation());
			} else {
				pstm.setBoolean(2, false);
				pstm.setString(3, "No Location");
			}
			pstm.setString(4, service.getURI());
			pstm.setBoolean(5, service.isRollbackable());
			pstm.setBoolean(6, service.isCondAction());
			pstm.setBoolean(7, service.isDependent());
			pstm.execute();

			if (service.isDependent()) {
				ServiceDependenciesDB
						.removeServiceDependenciesByDependentService(service
								.getURI());
				ServiceDependenciesDB.insertDependenciesByService(
						service.getURI(), service.getDependencies());
			}
		} catch (Exception e) {
			System.err.println("Erro: ServiceDB/updateService()");
			e.printStackTrace();
		} finally {
			pstm.close();
			connection.close();
		}

	}

	public static void removeService(ServicoSemantico service)
			throws SQLException {
		Connection connection = null;
		PreparedStatement pstm = null;
		servicoSemanticoLocator.remove(service.getURI());
		try {
			// removendo os parametros do serviço
			QualityParameterDB.removeParameterByService(service.getURI());

			// removendo o serviço em si
			connection = getConnection();
			String sql = "delete from service where uri = ?";
			pstm = connection.prepareStatement(sql);

			pstm.setString(1, service.getURI());

			pstm.execute();

			if (service.isDependent())
				ServiceDependenciesDB
						.removeServiceDependenciesByDependentService(service
								.getURI());

		} catch (Exception e) {
			System.err.println("Erro: ServiceDB/removeService()");
			e.printStackTrace();
		} finally {
			pstm.close();
			connection.close();
		}
	}

	public static ServicoSemantico getService(String uri) throws SQLException {
		ServicoSemantico service = servicoSemanticoLocator.get(uri);
		if (service != null) {
			log.debug("Return ServicoSemantico " + uri + " from Locator");
			return service;
		}
		// ServicoSemantico service = null;
		ResultSet rs = null;
		Connection conn = null;
		Statement st = null;

		try {
			conn = getConnection();
			st = conn.createStatement();

			String query = "select * from service where uri = '" + uri + "'";
			rs = st.executeQuery(query);
			if (rs.next()) {

				if (rs.getBoolean("iscontextservice")) {
					service = new ServicoSemanticoContexto();
					((ServicoSemanticoContexto) service).setLocation(rs
							.getString("location"));
				} else
					service = new ServicoSemantico();

				service.setNome(rs.getString("name"));
				service.setURI(rs.getString("uri"));

				// recuperando os metadados do servico. Note que o servico já é
				// retornado completo,
				// com seus dados básicos e seus metadados.
				service.setMetadata(QualityParameterDB
						.listParametersByService(service.getURI()));

				service.setRollbackable(rs.getBoolean("isrollbackable"));
				service.setCondAction(rs.getBoolean("iscompactionable"));
				service.setDependent(rs.getBoolean("isdependent"));

				if (service.isDependent())
					service.setDependencies(ServiceDependenciesDB
							.listServiceDependencies(service.getURI()));

			} else {
				throw new ServiceNotExistException("Service does not exist.");
			}
		} catch (ServiceNotExistException e) {
			return null;
		} catch (Exception e) {
			System.err.println("Erro:ServiceDB/getService()");
			e.printStackTrace();
		} finally {
			rs.close();
			st.close();
			conn.close();
		}
		servicoSemanticoLocator.put(uri, service);
		return service;

	}

}
